package com.mmy.dao.visa;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.visa.TVisaBase;

/**
 * 类的介绍：dao层 签证基础信息
 *
 * @author hanxl
 * @date 2020-07-23
 * @version 1.0
 */
public interface TVisaBaseRepository extends JpaRepository<TVisaBase, Integer>, JpaSpecificationExecutor<TVisaBase> {
	/* 查询所有签证信息不用写 自带 */
	/**
	 * 获得最大签证编号
	 * 
	 * @return 返回值说明 最大签证编码
	 * @author 创建人 dingxd 整理hanxl
	 * @date 创建日期 2020.11.17 时间2021.03.17
	 */
	@Query(value = "SELECT MAX(iVisaID) FROM tVisaBase WHERE LEFT(iVisaID,2) = RIGHT(YEAR(NOW()),2)", nativeQuery = true)
	Object getMaxVisaID();

	/**
	 * 创建
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.03.17
	 * 
	 */
	@Transactional
	@Modifying
	@Query(value = "INSERT INTO tVisaBase(iVisaID, iZoneID, cVisaName, iDay, fFloor, fPeer, fPrice, bType, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (?1,0, null, 0,0, 0, 0, 0, '2020-01-01 00:00:00', NULL, NULL, NULL, null, NULL, NULL, NULL, NULL, NULL)", nativeQuery = true)
	void save(int iVisaID);

	/**
	 * 通过区域ID查找是否已有签证存在
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.03.17
	 * 
	 */
	@Query(value = "select  * from tVisaBase where iZoneID =?1 and bType=?2", nativeQuery = true)
	List<TVisaBase> findByZoneID(int iZoneID, Byte bType);

	@Transactional
	@Modifying
	@Query(value = "select  * from tVisaBase where  tVisaBase.iVisaID = :iVisaID", nativeQuery = true)

	List<TVisaBase> findByiVisaID(@Param("iVisaID") String iVisaID);

	/**
	 * 通过签证ID查询
	 *
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.03.17
	 * 
	 */
	@Query(value = "select  * from tVisaBase where  iVisaID = :iVisaID", nativeQuery = true)
	TVisaBase findByiVisaID(@Param("iVisaID") int iVisaID);

	@Transactional
	@Modifying
	@Query(value = "update  tVisaBase  set  tVisaBase.cVisaName=?2 where   tVisaBase.iVisaID= ?1 ", nativeQuery = true)
	void update(int iVisaID, String cVisaName);

	@Transactional
	@Modifying
	@Query(value = "delete from tVisaBase where tVisaBase.iVisaID in (:iVisaIDs)", nativeQuery = true)
	void deleteChecked(@Param("iVisaIDs") List<Integer> iVisaIDs);

	@Transactional
	@Modifying
	@Query(value = "select  * from tVisaBase where  tVisaBase.cVisaName like %?1%", nativeQuery = true)
	List<TVisaBase> findBycVisaName(String cVisaName);

	/* void update(TVisaBase tVisaBase); */

}
